You are asked to demonstrate your recently acquired skills in risk management in a business context. The project will also prepare you gradually for the final Risk Management hedging project.

Framing the Problem

  • You were recently hired by a top tier Investment Bank, Insights Capital.
  • Insights Capital hires MBAs both for their expertise and professionalism in communication with clients.
  • Insights Capital was recently approached by SmartOil to produce a fact-based short paper to understand context and risk associated with Oil and Natural Gas in Canada.
  • SmartOil is a consortium of senior executives with a background in extracting industries and require this knowledge to better understand the economics of monetizing resources after a molecule is produced.

Skills Practiced & Requirements

  • Use of APIs for Enterprise workflow i.e. strictly no Excel, csv or use of other local files.

  • Data wrangling and parsing. You must use tidy data with long and wide data frames.

  • Choice of effective visualization in communication.

  • Data analytics and an ability to tell a story from it.

  • Foundational risk management skills.

  • Clean, clear and concise rendering of a business document. You may refer to quarto.org or any other online document to customize your output. I reserve the right to award a maximum of 50% for any document not properly presented. Some characteristics of a properly presented document:

    • A clear heading with title and who it is from.
    • Cleaning organized by business questions asked i.e. not a technical step rendering of your workflow.
    • Charts properly formatted and self explanatory.

  • Each question should be answered as if you were presenting to a client: you would not show code to a client!

  • Learning to learn:

    • Using new packages and functions as you see fit to achieve the desired outcome.
    • The arrow package with the use of the arrow::read_feather() function is a good example of a package that is not covered in class but is a good example of a package that is used in the industry to import data frames.

Whilst there are many packages to format tables the following are suggested:

Data

You will use the following sources of data:

  1. You will be using data from the US Energy Information Administration and Morningstar Commodities.

  2. The RTL::fizdiffs randomized data sets of Crude oil prices in key trading locations across North America:

    • Edmonton, AB.
    • Hardisty, AB.
    • Cushing, OK.
    • Houston (Nederland), TX.

  3. At this point in class, assume that the WCS grade is traded at a discount/premium to the WTI grade in Cushing, OK. Much in the same manner as if I tell you that the price of the benchmark apple pricing in international trading is the Fuji apple and the price of a Macintosh apple is traded at a discount/premium to the Fuji apple.

Simplified NA Energy Infrastructure Maps

Your Ask

  • Using quarto, render into a clean and concise html document. No .Rmd file will be accepted.
  • Name your file firstNameLastName.qmd and send it to pcote@ualberta.ca before the deadline.
  • This is strictly an individual exercise. Any attempt to communicate or cooperate with any other person will be deemed a violation of Academic Integrity and Honesty. For clarity this includes any online chat platforms (e.g. Discord) where I or the UoA might find evidence of communication in a formal investigation.
  • Any late submission will be assigned a grade of 0% - no questions asked.
  • You must use tidyverse workflow.
  • Points will be deducted if you don’t code with package::function() for all functions used in your code as well as loading unnecessary packages in your qmd file.

Part 1: Canadian Exports (2 points)

SmartOil needs context on the level and trends of Canadian exports to the US since 2009-01-01.

Use the following data from the EIA to tell the story.

You are free to use the arrow package to import the data, or use the RTL::eia2tidy() and/or RTL::eia2tidy_all() functions to extract the data via the EIA API. If you choose the latter, you will need to use the tickers are NG.N9102CN2.M and PET.MCRNTUSCA2.M respectively.

Part 2: Commodity Valuation (5 points)

The benchmark crude oil grade in Alberta is Western Canadian Select ("WCS"). SmartOil executives need factual information on the value of WCS a producer may obtain by selling in Alberta or exporting to Houston. This export additional value is then compared to the costs and risks of taking on pipeline shipper commitments.

SmartOil is only concerned with economics from 2019-01-01 as the Government of Alberta introduced production curtailment legislation in 2018 limiting downside risk.

Using the RTL::fizdiffs randomized dataset below, prepare the following in your report:

  1. Plot a time series of the values in Alberta, Houston and the price difference that could be realized by selling the crude in Houston instead of Alberta (a delivery location arbitrage).
  2. What would be the average gain of exporting from Alberta to Houston?
  3. Compute and discuss the average daily risk in dollars per barrel for the value in Alberta, Houston, and in the export arbitrage opportunity. SmartOil needs insights on the implications pertaining to a potential negotiation on seeking a pipeline commitment instead of selling WCS in Alberta.
RTL::fizdiffs %>% 
  dplyr::transmute(date = date,
                   Alberta = WTI.CMA01 + WCS.HDY,
                   Houston = WTI.CMA01 + WCS.HOU)
## # A tibble: 1,691 × 3
##    date       Alberta Houston
##    <date>       <dbl>   <dbl>
##  1 2017-01-03    37.8    45.3
##  2 2017-01-04    39.1    46.5
##  3 2017-01-05    39.7    47.1
##  4 2017-01-06    40.8    47.4
##  5 2017-01-09    39.0    45.2
##  6 2017-01-10    38.6    44.4
##  7 2017-01-11    39.8    45.7
##  8 2017-01-12    40.3    46.2
##  9 2017-01-13    39.8    45.6
## 10 2017-01-17    40.6    45.7
## # ℹ 1,681 more rows

Part 3: Delivery Timing (8 points)

SmartOil clients have at times to make decisions on temporary storage and all they see in the financial news headlines is the price of the WTI crude. As an additional example, you can find on Marketwatch Crude Oil Continuous Contract the same information on a different commercial data vendor. Hint: the first example of how to extract these type of contracts from Morningstar is in the tutorial on how to use Morningstar.

They are considering making decisions on storing from the 2nd to the 3rd contract month. You, as a specialist, know that there are futures contracts for different delivery months and using data from Morningstar from 2019-01-01 to 2024-01-31:

  1. Describe and explain the residual risk in using this futures contract to hedge WCS crude in Alberta or Houston.
  2. Chart the time series of this storage spread over time assuming zero costs of storage and financing. Indicate on the chart or in an explanation when the market is in contango or backwardation.
  3. With 2019-02-13 futures prices, compute whether it would economical to store assuming a financing cost of 4% per annum and monthly storage costs of $0.40 per barrel.
  4. As different firms may have different working capital costs, show a sensitivity chart of the value of storage versus working capital costs between 1% and 10%. Effectively, it would give the breakeven rate at which point storage economics are no longer economical. Use mapply() as covered in class or purrr::pmap() if you want to experiment. We will use the latter for more complex risk management problems at a later stage.

Part 4: Project Financing (5 points)

SmartOil often has to provide advice on matters of financing terms. In this specific case, the client has the choice of financing for:

  • A 3-year term that will then be refinanced at maturity for the remaining 7 years.
  • A full 10-year term.

The problem they have is that their usual decision-making process is of low quality: they have no idea as of today what the implied refinancing rate is and therefore engage in broad qualitative conversations around the future path of interest rates.

  1. Assuming interest rates are simple annual compounding, what is the implied 7-year annualized refinancing rate?
  2. Assuming interest rates are continuous compounding, what is the implied 7-year annualized refinancing rate?
RTLedu::bankOffer
## # A tibble: 5 × 2
##   maturity   rate
##      <dbl>  <dbl>
## 1        1 0.0539
## 2        3 0.0489
## 3        5 0.0479
## 4        7 0.0486
## 5       10 0.0486

Part 5 Financing Risk (10 points)

Insights Capital has also be retained to provide risk management advice to a Bank who may be involved in underwriting and holding the bonds issued by SmartOil. With your recent knowledge of interest rate risk, you are asked to provide a risk profile of the Bank’s portfolio of US Government bonds prior to entering into transaction with the company.

The Bank will be holding the following US bonds in its portfolio:

  • Long $1,000,000 of a 5-year bond.
  • Short $500,000 of a 30-year bond.
  • Short $500,000 of a 2-year bond.

All bonds are Constant Maturity Treasuries (CMT). You must use the relevant yield to maturities and coupons from FRED as of 2024-02-20.

You must:

  1. Compute and graph the delta and gamma central difference sensitivities of the portfolio to a 1 basis point change in yield across a \(\pm 2 \%\) range of yield changes. For clarity, your y-axis should be changes in the value of the portfolio in dollars and the x-axis should be the yield change in percentage points across that range where zero is the current ytm.
  2. You must explain the risk profile you see in the graph and the implications for the Bank’s portfolio. Remember you are drafting a consulting report and not a technical document sent to your Prof.